import pandas as pd
from datetime import datetime, timedelta

"""
按车辆汇总计算相交时间的总起始时间到总结束时间量
"""
# 读取 Excel 文件
file_path = 'ttt.xlsx'
df = pd.read_excel(file_path)

# 确保时间列为 datetime 类型
df['startTime'] = pd.to_datetime(df['startTime'])
df['endTime'] = pd.to_datetime(df['updateTime'])

# 按车辆分组
results = {}

for vehicle_id, group in df.groupby('vehicleId'):
    # 获取每个车辆的时间区间
    time_intervals = list(zip(group['startTime'], group['endTime']))

    # 合并交叉的区间
    merged_intervals = []
    for current in time_intervals:
        # 如果 merged_intervals 为空，直接添加
        if not merged_intervals:
            merged_intervals.append(current)
        else:
            last = merged_intervals[-1]
            if last[1] >= current[0] and last[0] <= current[1]:  # 如果有交叉
                new_start = min(last[0], current[0])
                new_end = max(last[1], current[1])
                merged_intervals[-1] = (new_start, new_end)
            else:
                merged_intervals.append(current)

    # 计算时间差
    total_duration = timedelta()
    for start, end in merged_intervals:
        total_duration += (end - start)

    # 存储结果
    results[vehicle_id] = total_duration.total_seconds() / 3600  # 以小时为单位

# 将结果转换为 DataFrame 以便于输出
result_df = pd.DataFrame(list(results.items()), columns=['vehicleId', 'total_duration_hours'])
# print(result_df)

# 将结果写入新的 Excel 文件
result_df.to_excel('车辆维修总时间.xlsx', index=False)